package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.rmt.data.AddCodeNDetail;
import com.mbc.rmt.data.RemittanceCashReceivedDataList;
import com.mbc.rmt.entities.CashReceiptRpt;
import com.mbc.rmt.entities.RemittanceCashReceived;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;

public class RemittanceCashReceivedDAOImpls extends DAO<RemittanceCashReceived> implements RemittanceCashReceivedDAO {

	public RemittanceCashReceivedDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "RemittanceCashReceived";
	}

	@Override
	public void create(RemittanceCashReceived entity) throws SQLException {
		query = "insert into " + table_name + " ( ReceivedId, ReceiptNo, CreatedDate, ReceivedDate, BranchId, IssuedBranchId, CreatedBy, PaidCurrencyId, RemitAmount, PaidAmount, ReceivedCurrenyId, Remark, ModifiedDate, ModifiedBy, ReceivedAmount, RefundAmount, ExtraFees, PaidBy, AddCodeId, Sender, Receiver, NRC, Address, Rate, RateOperator) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceivedId());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate().getTime()));
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getIssuedBranchId());
		pstmt.setLong(i++, entity.getCreatedBy());
		pstmt.setLong(i++, entity.getPaidCurrencyId());
		pstmt.setDouble(i++, entity.getRemitAmount());
		pstmt.setDouble(i++, entity.getPaidAmount());
		pstmt.setLong(i++, entity.getReceivedCurrenyId());
		pstmt.setString(i++, entity.getRemark());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setDouble(i++, entity.getReceivedAmount());
		pstmt.setDouble(i++, entity.getRefundAmount());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setLong(i++, entity.getPaidBy());
		pstmt.setLong(i++, entity.getAddCodeId());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setString(i++, entity.getRateOperator());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void udpate(RemittanceCashReceived entity) throws SQLException {
		query = "update " + table_name + " set ReceivedDate = ?, BranchId = ?, IssuedBranchId = ?, PaidCurrencyId = ?, RemitAmount = ?, PaidAmount = ?, ReceivedCurrenyId = ?, Remark = ?, ModifiedDate = ?, ModifiedBy = ?, ReceivedAmount = ?, RefundAmount = ?, ExtraFees = ?, PaidBy = ?, AddCodeId = ?, Sender = ?, Receiver = ?, NRC = ?, Address = ?, Rate = ?, RateOperator = ? where ReceivedId = " + entity.getReceivedId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate().getTime()));
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getIssuedBranchId());
		pstmt.setLong(i++, entity.getPaidCurrencyId());
		pstmt.setDouble(i++, entity.getRemitAmount());
		pstmt.setDouble(i++, entity.getPaidAmount());
		pstmt.setLong(i++, entity.getReceivedCurrenyId());
		pstmt.setString(i++, entity.getRemark());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setDouble(i++, entity.getReceivedAmount());
		pstmt.setDouble(i++, entity.getRefundAmount());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setLong(i++, entity.getPaidBy());
		pstmt.setLong(i++, entity.getAddCodeId());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setString(i++, entity.getRateOperator());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceivedId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public RemittanceCashReceived find(long key) throws SQLException {
		RemittanceCashReceived l_RemittanceCashReceived = null;
		query = "select a.*, b.Code, c.BankCode from " + table_name + " a" + 
				" inner join Addcode b on a.AddCodeId = b.AddCodeId" +
				" left join Bank c on a.PaidBy = c.BankId" +
				" where a.ReceivedId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_RemittanceCashReceived = readRecord(rs);
			l_RemittanceCashReceived.setAddCode(rs.getString("Code"));
			l_RemittanceCashReceived.setBankCode(rs.getString("BankCode") == null ? "" : rs.getString("BankCode"));
		}
		rs.close();
		pstmt.close();
		return l_RemittanceCashReceived;
	}

	@Override
	public ArrayList<RemittanceCashReceived> findAll() throws SQLException {
		ArrayList<RemittanceCashReceived> l_RemittanceCashReceivedList = new ArrayList<RemittanceCashReceived>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_RemittanceCashReceivedList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_RemittanceCashReceivedList;
	}

	@Override
	protected RemittanceCashReceived readRecord(ResultSet rs) throws SQLException {
		RemittanceCashReceived l_entity = new RemittanceCashReceived();
				l_entity.setReceivedId(rs.getLong("ReceivedId"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setIssuedBranchId(rs.getLong("IssuedBranchId"));
		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
		l_entity.setPaidCurrencyId(rs.getLong("PaidCurrencyId"));
		l_entity.setRemitAmount(rs.getDouble("RemitAmount"));
		l_entity.setPaidAmount(rs.getDouble("PaidAmount"));
		l_entity.setReceivedCurrenyId(rs.getLong("ReceivedCurrenyId"));
		l_entity.setRemark(rs.getString("Remark"));
		l_entity.setModifiedDate(rs.getTimestamp("ModifiedDate"));
		l_entity.setModifiedBy(rs.getLong("ModifiedBy"));
		l_entity.setReceivedAmount(rs.getDouble("ReceivedAmount"));
		l_entity.setRefundAmount(rs.getDouble("RefundAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setPaidBy(rs.getLong("PaidBy"));
		l_entity.setAddCodeId(rs.getLong("AddCodeId"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setRateOperator(rs.getString("RateOperator"));
		return l_entity;
	}

	@Override
	public AddCodeNDetail getLatestAddCodeNDetail(long addCodeId)
			throws SQLException {
		AddCodeNDetail l_data = null;
		query = "select AddCodeId, Sender, Receiver, NRC, Address from " + table_name + 
				" where AddCodeId = " + addCodeId + 
				" order by ReceivedId desc";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_data = new AddCodeNDetail();
			l_data.setAddCodeId(rs.getLong(1));
			l_data.setSender(rs.getString(2));
			l_data.setReceiver(rs.getString(3));
			l_data.setNrc(rs.getString(4));
			l_data.setAddress(rs.getString(5));
		}
		rs.close();
		pstmt.close();
		
		return l_data;
	}
	
	@Override
	public RemittanceCashReceivedDataList findRmtCashReceived(CriteriaParams cri)
			throws SQLException {
		RemittanceCashReceivedDataList l_DataList = new RemittanceCashReceivedDataList();
		ArrayList<RemittanceCashReceived> l_entityList = new ArrayList<RemittanceCashReceived>();
		
		String l_cri = " and (b.Code like N'%" + cri.getKeyword() + "%'" +
				" or d.Name like N'%" + cri.getKeyword() + "%'" +
				" or a.Sender like N'%" + cri.getKeyword() + "%'" + 
				" or a.ReceiptNo like N'%" + cri.getKeyword() + "%'" +
				" or a.Receiver like N'%" + cri.getKeyword() + "%'" +
				" or a.NRC like N'%" + cri.getKeyword() + "%')" +
				" and a.BranchId = " + cri.getBranchId();
		
		query = "set rowcount " + cri.getSize() +
				" select * from (select row_number() over(order by a.ReceivedId desc) as [rowno], a.*, b.Code, c.BankCode, d.Name as BranchName from " + table_name + " a" +  
				" inner join Addcode b on a.AddCodeId = b.AddCodeId" +
				" inner join Branch d on a.IssuedBranchId = d.BranchId" +
				" left join Bank c on a.PaidBy = c.BankId" +
				" where 1 = 1" + l_cri + ") " + table_name +
				" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {	
			RemittanceCashReceived l_entity = readRecord(rs);
			l_entity.setAddCode(rs.getString("Code"));
			l_entity.setBankCode(rs.getString("BankCode") == null ? "" : rs.getString("BankCode"));
			l_entity.setIssusedBranchName(rs.getString("BranchName"));
			l_entityList.add(l_entity);
		}
		rs.close();
		pstmt.close();
		l_DataList.setEntityList(l_entityList);
		query = "select count(a.ReceivedId) from " + table_name + " a" +  
				" inner join Addcode b on a.AddCodeId = b.AddCodeId" +
				" inner join Branch d on a.IssuedBranchId = d.BranchId" +
				" left join Bank c on a.PaidBy = c.BankId" +
				" where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public int getTotalReceipt(long branchId) throws SQLException {
		query = "select count(ReceiptNo) from " + table_name + " where BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		rs.next();
		int l_total = rs.getInt(1);
		rs.close();
		pstmt.close();
		return l_total;
	}
	
	//MBC Begin
	public ResultSet prepareCashReceiptReport(CashReceiptRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CashReceiptRpt ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashReceiptRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}
	private static void CashReceiptRptParameter(CallableStatement l_pstmt,
			CashReceiptRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromdate());
		l_pstmt.setString(i++, info.getTodate());
		l_pstmt.setString(i++, info.getFromReceiptNo());
		l_pstmt.setString(i++, info.getToReceiptNo());
		l_pstmt.setString(i++, info.getFromAddCodeID());
		l_pstmt.setString(i++, info.getToAddCodeID());
		l_pstmt.setString(i++, info.getFrompaidcurrencyID());
		l_pstmt.setString(i++, info.getTopaidcurrencyID());
		l_pstmt.setString(i++, info.getFromreceivedcurrencyID());
		l_pstmt.setString(i++, info.getToreceivedcurrencyID());
		l_pstmt.setString(i++, info.getGroupby());
		l_pstmt.setLong(i++, info.getBranchID());
	}
	//MBC End

	@Override
	public RemittanceCashReceived find(String receiptNo) throws SQLException {
		RemittanceCashReceived l_entity = null;
		query = " select a.*, b.Code from " + table_name + " as a " +
				" inner join AddCode as b on a.AddCodeId = b.AddCodeId" +
				" where a.ReceiptNo = '" + receiptNo + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_entity = readRecord(rs);
			l_entity.setAddCode(rs.getString("Code"));
		}
		rs.close();
		pstmt.close();
		return l_entity;
	}

}
